﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class WB03WBData : BaseFunction
    {


        public WB03WBData()
            : base()
        {

        }

        public WB03WBData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet BuildData(DataSet setResult, DataRow dwThis)
        {
           // DataSet setReturn = new DataSet();
            DataSet setUI = new DataSet();
            DataSet dsResult = new DataSet();
            /* + " UNION ALL "
                    + " select(CASE WHEN(substr(WBBART,length(trim(WBBART)))>'A') "
                    + " THEN trim(REPLACE(WBBART,SUBSTR(WBBART,length(trim(WBBART)),1),' '))||'-'||trim(WBBFARBE)||'Total:' "
                    + "  ELSE trim(WBBART)||'-'||trim(WBBFARBE)||'Total:' END ) AS ARTICLE, "
                    + " '' AS WBBBESCHICH,0 AS WBBEINDAT,'' AS WBBDPART, "
                    + " SUM(UNCHECKEDQTY) AS UNCHECKEDQTY,SUM(UNCHECKEDMTR) AS UNCHECKEDMTR, "
                    + " SUM(CHECKEDQTY) AS CHECKEDQTY,SUM(CHECKEDMTR) AS CHECKEDMTR, "
                    + " SUM(OTHERQTY) AS OTHERQTY,SUM(OTHERMTR) AS OTHERMTR ,"
                    + " SUM(ALLOCATEDQTY) AS ALLOCATEDQTY,SUM(ALLOCATEDMTR) AS ALLOCATEDMTR"
                    + " ,SUM(REWORKEDQTY) AS REWORKEDQTY,SUM(REWORKEDMTR) AS REWORKEDMTR "
                    + " FROM finish_inventory_summary  "
                    + " WHERE (TRIM(WBBART) = :ARTICLE OR :ARTICLE = 'A') "
                    + " AND (TRIM(WBBDPART) = :WBBDPART OR :WBBDPART = 'A' ) "
                    + " GROUP BY (CASE WHEN(substr(WBBART,length(trim(WBBART)))>'A') "
                    + "  THEN trim(REPLACE(WBBART,SUBSTR(WBBART,length(trim(WBBART)),1),' '))||'-'||trim(WBBFARBE)||'Total:' "
                    + "  ELSE trim(WBBART)||'-'||trim(WBBFARBE)||'Total:' END ) 
             */
            try
            { 
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select (CASE WHEN(substr(WBBART,length(trim(WBBART)))>'A') "
                    + " THEN trim(REPLACE(WBBART,SUBSTR(WBBART,length(trim(WBBART)),1),' '))||'-'||trim(WBBFARBE) "
                    + " ELSE trim(WBBART)||'-'||trim(WBBFARBE) END ) AS ARTICLE, "
                    + " WBBBESCHICH,COLORDES,WBBEINDAT,WBBDPART, "
                    + " UNCHECKEDQTY,UNCHECKEDMTR,CHECKEDQTY,CHECKEDMTR,OTHERQTY,OTHERMTR,ALLOCATEDQTY,ALLOCATEDMTR "
                    + " ,REWORKEDQTY,REWORKEDMTR "
                    + " from finish_inventory_summary  "
                    + " WHERE (TRIM(WBBART) = :ARTICLE OR :ARTICLE ='A')  "
                    + " AND (TRIM(WBBDPART) = :WBBDPART OR :WBBDPART ='A') "
                    + " ORDER BY 1  ";
                orlReadCommand.Parameters.Add("ARTICLE", OracleType.NVarChar).Value = dwThis["ARTICLE"].ToString();
                orlReadCommand.Parameters.Add("WBBDPART", OracleType.NVarChar).Value = dwThis["BATCH"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "FINISH_INVENTORY_SUMMARY";
                    byte[] b;
                    string value;
                    string ColumnName;
                    foreach (DataRow dw in dsResult.Tables["FINISH_INVENTORY_SUMMARY"].Rows)
                    {
                        ColumnName = "WBBBESCHICH";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;
                        ColumnName = "ARTICLE";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;
                        ColumnName = "COLORDES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;
                    }
                    
                }
                setResult.Merge(dsResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setResult;
        }
        public DataSet BuildData1(DataSet setResult, DataRow dwThis)
        {
            // DataSet setReturn = new DataSet();
            DataSet dsResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select trim(WBBART)||'-'||trim(WBBFARBE) AS ARTICLE, "
                    + " WBBBESCHICH,WBBEINDAT,WBBDPART, "
                    + " UNCHECKEDQTY,UNCHECKEDMTR,CHECKEDQTY,CHECKEDMTR,OTHERQTY,OTHERMTR,ALLOCATEDQTY,ALLOCATEDMTR "
                    + " ,REWORKEDQTY,REWORKEDMTR "
                    + " from finish_inventory_summary  "
                    + " WHERE (TRIM(WBBART) = :ARTICLE OR :ARTICLE ='A')  "
                    + " AND (TRIM(WBBDPART) = :WBBDPART OR :WBBDPART ='A') ";
                orlReadCommand.Parameters.Add("ARTICLE", OracleType.NVarChar).Value = dwThis["ARTICLE"].ToString();
                orlReadCommand.Parameters.Add("WBBDPART", OracleType.NVarChar).Value = dwThis["BATCH"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "FINISH_INVENTORY_SUMMARY";
                    byte[] b;
                    string value;
                    string ColumnName;
                    foreach (DataRow dw in dsResult.Tables["FINISH_INVENTORY_SUMMARY"].Rows)
                    {
                        ColumnName = "WBBBESCHICH";

                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;
                        ColumnName = "ARTICLE";

                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;

                    }
                }
                setResult.Merge(dsResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setResult;
        }
    }
}